package com.hxz.dao;

import com.hxz.entity.User;
import com.hxz.utils.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 数据库操作层
 */
public class UserDao {

    /**
     * 分页查询
     * @param username 用户账号
     * @param pageIndex 第几页
     * @param pageSize 页数
     * @return 用户列表
     */
    public List<User> getUsers(String username, int pageIndex, int pageSize) {
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        try {
            //1.获取连接，设置sql语句
            conn = JDBCUtil.getConn();
            String sql = "select * from user where 1=1 ";
            if (username != null && !username.equals("")) {
                sql += " and username = ? ";
            }
            sql += " limit " + (pageIndex -1)* pageSize + "," + pageSize;
            System.out.println("sql:" + sql);
            ps = conn.prepareStatement(sql);
            //2.设置参数，代替问号
            if (username != null && !username.equals("")) {
                ps.setString(1, username);
            }
            //3.执行sql语句
            rs = ps.executeQuery();
            List<User> list = new ArrayList<>();
            //4.结果集处理
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setPhone(rs.getString(4));
                list.add(user);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(rs, ps, conn);
        }
        return null;
    }

    /**
     * 添加用户信息
     * @param user 用户
     */
    public void addUser(User user) {
        PreparedStatement ps = null;
        Connection conn = null;
        int ret = -1;
        try {
            conn = JDBCUtil.getConn();
            String sql = "insert into user(username, password, phone) values (?,?,?)";
            ps = conn.prepareStatement(sql);
            //设置参数，代替问号?
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getPhone());
            //执行sql语句
            ret = ps.executeUpdate();
            System.out.println("ret=" + ret);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(null, ps, conn);
        }
    }

    /**
     * 修改用户信息
     * @param user 用户
     */
    public void editUser(User user) {
        PreparedStatement ps = null;
        Connection conn = null;
         try {
             conn = JDBCUtil.getConn();
             String sql = "update user set username = ?, password = ?, phone = ? where id = ?";
             ps = conn.prepareStatement(sql);
             //设置参数，替代问号
             ps.setString(1, user.getUsername());
             ps.setString(2, user.getPassword());
             ps.setString(3, user.getPhone());
             ps.setInt(4, user.getId());
             //执行sql
             ps.executeUpdate();
         } catch (SQLException e) {
             e.printStackTrace();
         } finally {
             JDBCUtil.closeAll(null, ps, conn);
         }
    }
    /**
     * 根据id删除用户记录
     * @param id 用户id
     */
    public void deleteUser(int id) {
        PreparedStatement ps = null;
        Connection conn = null;
        try {
            conn = JDBCUtil.getConn();
            String sql = "delete from user where id = ?";
            ps = conn.prepareStatement(sql);
            // 设置参数，代替问号
            ps.setInt(1, id);
            //执行sql语句
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(null, ps, conn);
        }
    }
    /**
     * 根据id获取用户记录
     * @param id 用户id
     * @return 用户记录
     */
    public User getOne(int id) {
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        try {
            //1.获取连接，设置sql语句
            conn = JDBCUtil.getConn();
            String sql = "select * from user where id = ?";
            ps = conn.prepareStatement(sql);
            //2，设置参数，代替问号
            ps.setInt(1, id);
            //3，执行sql语句
            rs = ps.executeQuery();
            User user = new User();
            //4.结果值处理
            while (rs.next()) {
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setPhone(rs.getString(4));
            }
            return  user;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeAll(rs, ps, conn);
        }
        return null;
    }
}
